package net.hn.hnms.biz.intelligence.service;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.Dict;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import lombok.RequiredArgsConstructor;
import net.hn.hnms.biz.intelligence.util.DateRangeUtil;
import net.hn.hnms.sys.common.core.domain.ResponsePage;
import net.hn.hnms.sys.common.core.utils.DateUtils;
import net.hn.hnms.biz.intelligence.vo.*;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

import static net.hn.hnms.sys.common.core.utils.DateUtils.YYYY_MM_DD_HH_MM_SS;

@Service
@RequiredArgsConstructor
public class SafetyIntelligenceServiceImpl implements SafetyIntelligenceService {
    @Qualifier("jdbcTemplate")
    private final JdbcTemplate jdbcTemplate;
    @Qualifier("testJdbcTemplate")
    private final JdbcTemplate testJdbcTemplate;

    @Override
    public ResponsePage<CollectGasAlarmVO> collectGasAlarm(int pageNum, int pageSize,
                                                           String mineCode, Date startTime, Date endTime,
                                                           String outlierType) {

        String strStartTime = DateUtils.parseDateToStr(YYYY_MM_DD_HH_MM_SS, startTime);
        String strEndTime = DateUtils.parseDateToStr(YYYY_MM_DD_HH_MM_SS, endTime);

        String sql = "SELECT %s FROM znjc.dws_his_sensor_exception_judge_day where 1=1 ";

        String columns = "day_date as dataDate, mine_code as code, sensor_code as pointCode, exception_type as OutlierType, " +
                "data_time as beginTime, sensor_type_name as sensorTypeName, sensor_location as pointLocation ";
        // 查询条件
        StringBuilder sb = new StringBuilder(sql);
        if (StringUtils.isNotBlank(strStartTime)) {
            sb.append(" and data_time >= to_timestamp('").append(strStartTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        if (StringUtils.isNotBlank(strEndTime)) {
            sb.append(" and data_time < to_timestamp('").append(strEndTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        if (StringUtils.isNotBlank(outlierType)) {
            sb.append(" and exception_type = '").append(outlierType).append("' ");
        }
        if (StringUtils.isNotBlank(mineCode)) {
            sb.append(" and mine_code in ( '").append(getMineCodes(mineCode)).append("')");
        }

        Integer count = jdbcTemplate.queryForObject(String.format(sb.toString(),"count(1)"), Integer.class);
        ResponsePage<CollectGasAlarmVO> responsePage;
        if (count > 0) {
            // 分页
            sb.append(" order by data_time desc  limit ").append(pageSize).append(" offset ").append(pageNum * pageSize);
            List<CollectGasAlarmVO> query = jdbcTemplate.query(String.format(sb.toString(), columns),
                    new BeanPropertyRowMapper<>(CollectGasAlarmVO.class));
            query.forEach(alarmVO -> {
                alarmVO.setName(setMineName(alarmVO.getCode()));
            });
            responsePage = new ResponsePage<>(query, count);
        } else {
            responsePage = new ResponsePage<>(new ArrayList(), 0);
        }
        return responsePage;

    }

    @Override
    public Map<String, Object> querySensorValue(String pointCode, Date startTime, Date endTime, String outlierType) {

        Map<String, String> EXCEPTION_TYPE_MAP = new HashMap<String, String>() {
            {
                put("001", "中断异常");
                put("002", "数值异常");
                put("003", "疑似遮断");
            }
        };

        String strStartTime = DateUtil.formatDateTime(startTime);
        String strEndTime = DateUtil.formatDateTime(endTime);

        String sql = "SELECT mine_code as mineCode, point_code as pointCode, data_time as dataTime, " +
                "sensor_type as sensorType, sensor_type_name as sensorTypeName, " +
                "point_location as pointLocation, point_value as pointValue, point_status as pointStatus, " +
                "value_unit as valueUnit FROM aqjk.dwd_aqjk_his_sensormonitor where 1=1 ";
        StringBuilder sb = new StringBuilder(sql);
        if (StringUtils.isNotBlank(pointCode)) {
            sb.append(" and point_code = '").append(pointCode).append("' ");
        }
        if (StringUtils.isNotBlank(strStartTime)) {
            sb.append(" and data_time >= to_timestamp('").append(strStartTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        if (StringUtils.isNotBlank(strEndTime)) {
            sb.append(" and data_time < to_timestamp('").append(strEndTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        sb.append(" order by data_time ");
        // 详情列表数据
        List<SensorValuesVO> query = testJdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(SensorValuesVO.class));

        ChartSensorValueVO chartSensorValueVO = new ChartSensorValueVO();
        List<String> xName = new ArrayList<>();
        List<BigDecimal> pointValue = new ArrayList<>();
        List<BigDecimal> maxPointValue = new ArrayList<>();
        List<BigDecimal> minPointValue = new ArrayList<>();
        List<String> pointStatus = new ArrayList<>();
        List<String> pointStatusName = new ArrayList<>();
        List<SensorValuesVO> sensorValuesVO = new ArrayList<>();

        if (query.size() > 0) {
            sensorValuesVO = query;
            query.forEach(x -> {
                xName.add(String.valueOf(x.getDataTime()));
                pointValue.add(BigDecimal.valueOf(Double.parseDouble(x.getPointValue())));
                maxPointValue.add(BigDecimal.valueOf(Double.parseDouble(x.getPointValue())));
                minPointValue.add(BigDecimal.valueOf(Double.parseDouble(x.getPointValue())));
                pointStatus.add(x.getPointStatus());
                pointStatusName.add(x.getPointStatusName());
            });
            chartSensorValueVO.setSensorValuesVO(sensorValuesVO);
            chartSensorValueVO.setXName(xName);
            chartSensorValueVO.setPointValue(pointValue);
            chartSensorValueVO.setMaxPointValue(maxPointValue);
            chartSensorValueVO.setMinPointValue(minPointValue);
            chartSensorValueVO.setPointStatus(pointStatus);
            chartSensorValueVO.setPointStatusName(pointStatusName);

        }
        String sqlAlarm = "SELECT max_value as maxValue,maxvalue_time as maxValueTime, begin_time as beginTime, " +
                "end_time as endTime, outlier_type, extract (epoch from  (end_time-begin_time)) as duration  " +
                "from aqjk.dwd_aqjk_his_sensoralarm " +
                "where 1=1 ";
//                "and outlier_type in (?4) " +
//                "      and ((`sensor_type` NOT IN ( '0011', '0043' )) OR ((`sensor_type` IN ( '0011', '0043' )) AND ( `outlier_type` != '001' )) " +
//                "                         OR ((`sensor_type` = '0011'  AND  `outlier_type` = '001' ) AND (`max_value` >= 0.5 ))" +
//                "                       OR ((`sensor_type` = '0043'  AND  `outlier_type` = '001' ) AND (`max_value` >= 0.5 )) ) " +

        StringBuilder sbAlarm = new StringBuilder(sqlAlarm);
        if (StringUtils.isNotBlank(pointCode)) {
            sbAlarm.append(" and point_code = '").append(pointCode).append("' ");
        }
        if (StringUtils.isNotBlank(strStartTime)) {
            sbAlarm.append(" and begin_time >= to_timestamp('").append(strStartTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        if (StringUtils.isNotBlank(strEndTime)) {
            sbAlarm.append(" and begin_time < to_timestamp('").append(strEndTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        sbAlarm.append(" order by begin_time ");
        List<AlarmData> queryAlarm = testJdbcTemplate.query(sbAlarm.toString(), new BeanPropertyRowMapper<>(AlarmData.class));
        queryAlarm.forEach(q -> {
            if (q.getOutlierType() != null) q.setOutlierTypeName(EXCEPTION_TYPE_MAP.get(q.getOutlierType()));
        });
        List<Object> content = new ArrayList<>();
        content.add(chartSensorValueVO);
        content.add(Dict.create().set("alarmData", queryAlarm));
        return Dict.create().set("content", content).set("totalElements", content.size());
    }

    @Override
    public ResponsePage<CollectSuspectedSleepDutyVO> collectSuspectedSleepDuty(
            int pageNum, int pageSize, String mineCode, Date startTime, Date endTime, Double continueTimeLength) {
        String strStartTime = DateUtils.parseDateToStr(YYYY_MM_DD_HH_MM_SS, startTime);
        String strEndTime = DateUtils.parseDateToStr(YYYY_MM_DD_HH_MM_SS, endTime);

        String sql = "SELECT %s FROM znjc.dws_his_leader_exception_judge_day where 1=1 ";
        String columns = " mine_code as mineCode, staff_code as staffCode, staff_name as staffName, well_time as wellTime, " +
                "before_area_in_time as beginTime, after_area_in_time as endTime, day_date as dataDate," +
                " extract (epoch from  (after_area_in_time-before_area_in_time)) as duration ";
        StringBuilder sb = new StringBuilder(sql);
        if (StringUtils.isNotBlank(strStartTime)) {
            sb.append(" and before_area_in_time >= to_timestamp('").append(strStartTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        if (StringUtils.isNotBlank(strEndTime)) {
            sb.append(" and before_area_in_time < to_timestamp('").append(strEndTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        if (continueTimeLength > 0 ) {
            sb.append(" and EXTRACT(MINUTE FROM (after_area_in_time)-(before_area_in_time)) >= ").append(continueTimeLength);
        }
        if (StringUtils.isNotBlank(mineCode)) {
            sb.append(" and mine_code in ( '").append(getMineCodes(mineCode)).append("')");

        }

        Integer count = jdbcTemplate.queryForObject(String.format(sb.toString(),"count(1)"), Integer.class);
        ResponsePage<CollectSuspectedSleepDutyVO> responsePage;
        if (count > 0) {

            sb.append(" order by before_area_in_time desc  limit ").append(pageSize).append(" offset ").append(pageNum * pageSize);
            List<CollectSuspectedSleepDutyVO> query = jdbcTemplate.query(String.format(sb.toString(), columns),
                    new BeanPropertyRowMapper<>(CollectSuspectedSleepDutyVO.class));
            if (query.size() > 0) {
                // 获取人员基础信息，workType用duty代替
                List<String> staffCodeList = new ArrayList<>();
                query.forEach(q -> {
                    staffCodeList.add(q.getStaffCode());
                });
                String strStaffCode = String.join("','", staffCodeList);
                List<StaffInfoVO> staffInfoList = testJdbcTemplate.query("select staff_code as staffCode, " +
                                "staff_name as starffName, dept_name as departmentName, duty as workType, mine_leader as isLeader " +
                                "from jxry.dwd_jxry_base_personnelinfo where staff_code in ('" + strStaffCode + "')",
                        new BeanPropertyRowMapper<>(StaffInfoVO.class));

                Map<String, StaffInfoVO> staffInfoMap =  new HashMap<>();
                staffInfoList.forEach(item -> {
                    staffInfoMap.put(item.getStaffCode(), item);
                });
                query.forEach(q -> {
                    if (staffInfoMap.get(q.getStaffCode()) != null) {
                        q.setDepartmentName(staffInfoMap.get(q.getStaffCode()).getDepartmentName());
                        q.setWorkType(staffInfoMap.get(q.getStaffCode()).getWorkType());
                        q.setIsLeader(staffInfoMap.get(q.getStaffCode()).getIsLeader());
                    }
                    q.setMineName(setMineName(String.valueOf(q.getMineCode())));

                });
            }
            responsePage = new ResponsePage<CollectSuspectedSleepDutyVO>(query, count);
        } else {
            responsePage = new ResponsePage<CollectSuspectedSleepDutyVO>(new ArrayList(), 0);
        }
        return responsePage;
    }

    @Override
    public List<PersonTraceVO> personTrace(String orgId, String staffCode, Date wellTime, Date wellOutTime) {

        String strWellTime = DateUtil.formatDateTime(wellTime);
        String strWellOutTime = DateUtil.formatDateTime(wellOutTime);

        String sql = "SELECT p.staff_code as staffCode, p.staff_name as staffName, p.in_time as wellTime, " +
                "p.area_code as locstationCode, p.area_in_time as locstationIntime, a.area_name as locstationLocation, p.mine_code " +
                "FROM jxry.dwd_jxry_his_personnelinfo p left join (  SELECT  distinct code, area_name, mine_code\n" +
                "FROM jxry.dwd_jxry_his_personnelareainfo) a on p.area_code = a.code and a.mine_code =p.mine_code  " +
                "where 1=1";

        // 查询条件
        StringBuilder sb = new StringBuilder(sql);
        if (StringUtils.isNotBlank(strWellTime)) {
            sb.append(" and p.staff_code = '").append(staffCode).append("'");
        }
        if (StringUtils.isNotBlank(strWellTime)) {
            sb.append(" and p.in_time = to_timestamp('").append(strWellTime).append("','yyyy-mm-dd hh24:mi:ss') ");
            sb.append(" and p.area_in_time >= to_timestamp('").append(strWellTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        if (StringUtils.isNotBlank(strWellOutTime)) {
            sb.append(" and p.area_in_time <= to_timestamp('").append(strWellOutTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        } else {
            sb.append(" and p.area_in_time <= p.area_out_time ");
        }
       sb.append("order by p.area_in_time ");

        List<PersonTraceVO> query = testJdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(PersonTraceVO.class));
        return query;
    }

    @Override
    public ResponsePage<HydrogeoAmountAlarmStatisticsVO> collectHydrogeoAmountAlarmStatistics(
            int pageNum, int pageSize, String mineCode, Date startTime, Date endTime, String alarmType, String handleFlag) {
        Map<String, String> ALARM_TYPE_MAP = new HashMap<String, String>() {
            {
                put("1", "日降雨量50毫米及以上");
                put("2", "累计3天降雨量超过150毫米");
                put("3", "涌水量24小时增加50%");
                put("4", "长观孔24小时水位变化超20%及以上");
            }
        };

        Map<String, String> ALARM_DESC_MAP = new HashMap<String, String>() {
            {
                put("31", "涌水量24小时内增加50%");
                put("32", "近24-48小时涌水量增加50%");
            }
        };
        // 水文类型，不知道从哪取值
        Map<String, String> HYDROGEO_TYPE_MAP = new HashMap<String, String>() {
            {
                put("10", "简单");
                put("20", "中等");
                put("30", "复杂");
                put("40", "极复杂");
            }
        };

        String strStartTime = DateUtil.formatDateTime(startTime);
        String strEndTime = DateUtil.formatDateTime(endTime);

        String sql = "SELECT %s FROM znjc.dws_shfz_water_level_analysis where 1=1 ";
        String columns = "data_time as dataTime, mine_code as code, point_code as pointCode, " +
                "analysis_alarm_type as analysisAlarmType, alarm_desc as alarmDesc, " +
                "sensor_type as sensorType, sensor_location as sensorLocation, point_status as pointStatus, " +
                "point_value as alarmValue, analysis_alarm_name as analysisAlarmTypeName, " +
                "base_line_data_time as baseLineDataTime, base_line_point_value as baseLinePointValue, create_time as createTime ";
        StringBuilder sb = new StringBuilder(sql);
        if (StringUtils.isNotBlank(strStartTime)) {
            sb.append(" and data_time >= '").append(strStartTime).append("' ");
        }
        if (StringUtils.isNotBlank(strEndTime)) {
            sb.append(" and data_time < '").append(strEndTime).append("' ");
        }
        if (StringUtils.isNotBlank(alarmType)) {
            sb.append(" and analysis_alarm_type ='").append(alarmType).append("' ");
        }
        if (StringUtils.isNotBlank(mineCode)) {
            sb.append(" and mine_code in ('").append(getMineCodes(mineCode)).append("' )");
        }

        Integer count;
        count = jdbcTemplate.queryForObject(String.format(sb.toString(),"count(1)"), Integer.class);
        ResponsePage<HydrogeoAmountAlarmStatisticsVO> responsePage;
        if (count > 0) {

            sb.append(" order by data_time desc limit ").append(pageSize).append(" offset ").append(pageNum * pageSize);
            List<HydrogeoAmountAlarmStatisticsVO> query = jdbcTemplate.query(String.format(sb.toString(), columns),
                    new BeanPropertyRowMapper<>(HydrogeoAmountAlarmStatisticsVO.class));
            if (query.size() > 0) {
                // todo 获取井下人数 水害地质类型编码 + 水害地质类型名称

                query.forEach(q -> {
                    if (q.getAnalysisAlarmType().equals(Byte.valueOf("3"))) {
                        q.setAlarmValue(q.getAlarmValue() + "m³/h");
                        q.setBaseLinePointValue(q.getBaseLinePointValue() + "m³/h");
                    } else {
                        q.setAlarmValue(q.getAlarmValue() + "m");
                        q.setBaseLinePointValue(q.getBaseLinePointValue() + "m");
                    }
                    // 报警描述
                    q.setAlarmDesc(ALARM_DESC_MAP.get(String.valueOf(q.getAlarmDesc())));
                    q.setName(setMineName(q.getCode()));
                    q.setStaffNum(0);
                });

            }
            responsePage = new ResponsePage<HydrogeoAmountAlarmStatisticsVO>(query, count);
        } else {
            responsePage = new ResponsePage<HydrogeoAmountAlarmStatisticsVO>(new ArrayList(), 0);
        }
        return responsePage;
    }

    @Override
    public Map<String, Object> chartSumPersonCountForMineCode(String mineCode, Date startTime, Date endTime) {


        String strStartTime = DateUtil.formatDateTime(startTime);
        String strEndTime = DateUtil.formatDateTime(endTime);

        String sql = "select concat(mine_code,left(cast(data_time as varchar), 19)) as id, mine_code as mineCode, " +
                "data_time as dataTime, count (distinct staff_code) as personCount, " +
                "sum(case special_personnel when '1' then 1 else 0 end) as specialCount, sum(case mine_leader when '1' then 1 else 0 end) as leaderCount " +
                "from ( " +
                "select mine_code,concat(left(cast(data_time as varchar), 15),'0:00') as data_time, staff_code, special_personnel, mine_leader " +
                "from jxry.dwd_jxry_his_personnelinfo " +
                "where 1=1 " +
                " %s " +
                ") a " +
                "group by mine_code, data_time " +
                "order by data_time ";

        // 查询条件
        String condition = "";
        StringBuilder sb = new StringBuilder(sql);
        if (StringUtils.isNotBlank(strStartTime)) {
            condition += " and data_time >= to_timestamp('" + strStartTime + "','yyyy-mm-dd hh24:mi:ss') ";
        }
        if (StringUtils.isNotBlank(strEndTime)) {
            condition += " and data_time < to_timestamp('" + strEndTime + "','yyyy-mm-dd hh24:mi:ss') ";
        }
        if (StringUtils.isNotBlank(mineCode)) {
            condition += " and mine_code in ( '" + getMineCodes(mineCode) +"') ";
        }


        List<PersonCountChartBO> query = testJdbcTemplate.query(String.format(sb.toString(), condition), new BeanPropertyRowMapper<>(PersonCountChartBO.class));

        ArrayList<ChartPersonCountVO> result = new ArrayList<>();
        ArrayList<String> xNames = new ArrayList<>();
//        xNames = DateRangeUtil.getDateRange(strStartTime, strEndTime, "yyyy-MM-dd");
        ArrayList<Integer> personCounts = new ArrayList<>();
        ArrayList<Integer> leaderCounts = new ArrayList<>();
        ArrayList<Integer> specialCounts = new ArrayList<>();

        query.forEach(q -> {
            xNames.add(q.getDataTime());
            leaderCounts.add(q.getLeaderCount());
            specialCounts.add(q.getSpecialCount());
            personCounts.add(q.getPersonCount());
        });

        ChartPersonCountVO personCountVO = new ChartPersonCountVO();
        personCountVO.setLeaderCount(leaderCounts);
        personCountVO.setPersonCount(personCounts);
        personCountVO.setSpecialCount(specialCounts);
        personCountVO.setXName(xNames);

        // todo: 暂时找不到煤矿核定人数信息
        personCountVO.setJxrySpecifyNum(0);
        personCountVO.setMineMaxNum(0);
        result.add(personCountVO);
        return Dict.create().set("content", result).set("totalElements", xNames.size());
    }

    @Override
    public Map<String, Object> chartStatisticsInOutStaff(String mineCode, Date startTime, Date endTime) {
        String strStartTime = DateUtil.formatDateTime(startTime);
        String strEndTime = DateUtil.formatDateTime(endTime);

        String sql = "select datetime as dataTime, sum(case when flag_bit ='2' then staffcount else 0 end) as outCount, " +
                "sum(case when flag_bit ='1' then staffcount else 0 end) as inCount " +
                "from( " +
                "select concat(left(cast(out_time as varchar), 15),'0:00') as dateTime, " +
                " count(*) as staffCount, " +
                " flag_bit as flag_bit  " +
                " from (  " +
                " select distinct staff_code, mine_code, out_time, flag_bit from jxry.dwd_jxry_his_personnelinfo  " +
                " where flag_bit = '2' %s  " +
                " and out_time is not null ) t " +
                " group by mine_code, concat(left(cast(out_time as varchar), 15),'0:00'), flag_bit  " +
                "  union  " +
                " select concat(left(cast(in_time as varchar), 15),'0:00') as dateTime,  " +
                " count(*) as  staffCount,  " +
                " flag_bit as flag_bit  " +
                " from   " +
                " (  " +
                "  select distinct staff_code, mine_code, in_time, flag_bit from jxry.dwd_jxry_his_personnelinfo  " +
                "  where flag_bit = '1' and in_time is not null  %s " +
                " ) t   " +
                " group by mine_code, concat(left(cast(in_time as varchar), 15),'0:00'), flag_bit " +
                ") a group by datetime";

        // 查询条件
        StringBuilder sb = new StringBuilder(sql);
        String conditionsIn = "";
        String conditionsOut = "";
        if (StringUtils.isNotBlank(strStartTime)) {
            conditionsIn = conditionsIn + " and in_time >= to_timestamp('" + strStartTime + "','yyyy-mm-dd hh24:mi:ss') ";
            conditionsOut = conditionsOut + " and out_time >= to_timestamp('" + strStartTime + "','yyyy-mm-dd hh24:mi:ss') ";
        }
        if (StringUtils.isNotBlank(strEndTime)) {
            conditionsIn = conditionsIn + " and in_time < to_timestamp('" + strEndTime + "','yyyy-mm-dd hh24:mi:ss') ";
            conditionsOut = conditionsOut + " and out_time < to_timestamp('" + strEndTime + "','yyyy-mm-dd hh24:mi:ss') ";
        }
        if (StringUtils.isNotBlank(mineCode)) {
            conditionsIn = conditionsIn +" and mine_code in ( '" + getMineCodes(mineCode) + "' )";
            conditionsOut = conditionsOut +" and mine_code in ( '" + getMineCodes(mineCode) + "' )";
        }
        sb.append(" order by datetime ");

        List<PersonCountChartBO> query = testJdbcTemplate.query(String.format(sb.toString(), conditionsOut, conditionsIn),
                new BeanPropertyRowMapper<>(PersonCountChartBO.class));

        ArrayList<ChartPersonInOutCountVO> result = new ArrayList<>();
        ArrayList<String> xNames = new ArrayList<>();
        ArrayList<Integer> inCount = new ArrayList<>();
        ArrayList<Integer> outCount = new ArrayList<>();

        query.forEach(q -> {
            xNames.add(q.getDataTime());
            inCount.add(q.getInCount());
            outCount.add(q.getOutCount());
        });

        ChartPersonInOutCountVO personCountVO = new ChartPersonInOutCountVO();
        personCountVO.setOutStaffCount(outCount);
        personCountVO.setInStaffCount(inCount);
        personCountVO.setXName(xNames);

        result.add(personCountVO);
        return Dict.create().set("content", result).set("totalElements", xNames.size());
    }

    @Override
    public ResponsePage<CollectPersonWellOutAbnormalCountVO> collectPersonWellOutAbnormalCount(
            int pageNum, int pageSize, String mineCode, Date startTime, Date endTime, String lowerLimit) {
        String strStartTime = DateUtil.formatDateTime(startTime);
        String strEndTime = DateUtil.formatDateTime(endTime);

        String sql = "SELECT %s FROM znjc.dws_jxry_his_wellouttime_statistics where 1=1 ";
        String columns = " mine_code as mineCode, well_outtime as wellOutTime, staff_num as wellOutPersonnel";
        StringBuilder sb = new StringBuilder(sql);
        if (StringUtils.isNotBlank(strStartTime)) {
            sb.append(" and well_outtime >= to_timestamp('").append(strStartTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        if (StringUtils.isNotBlank(strEndTime)) {
            sb.append(" and well_outtime < to_timestamp('").append(strEndTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        if (StringUtils.isNotBlank(lowerLimit)) {
            sb.append(" and staff_num >= ").append(lowerLimit);
        }
        if (StringUtils.isNotBlank(mineCode)) {
            sb.append(" and mine_code in ( '").append(getMineCodes(mineCode)).append("' )");
        }

        Integer count = jdbcTemplate.queryForObject(String.format(sb.toString(),"count(1)"), Integer.class);
        ResponsePage<CollectPersonWellOutAbnormalCountVO> responsePage;
        if (count > 0) {

            sb.append(" order by well_outtime desc  limit ").append(pageSize).append(" offset ").append(pageNum * pageSize);
            List<CollectPersonWellOutAbnormalCountVO> query = jdbcTemplate.query(String.format(sb.toString(), columns),
                    new BeanPropertyRowMapper<>(CollectPersonWellOutAbnormalCountVO.class));
            if (query.size() > 0) {
                query.forEach(q -> {
                    q.setMineName(setMineName(String.valueOf(q.getMineCode())));
                    q.setCountyName(setCompanyName(String.valueOf(q.getMineCode())));
                });
            }
            responsePage = new ResponsePage<>(query, count);
        } else {
            responsePage = new ResponsePage<>(new ArrayList(), 0);
        }
        return responsePage;
    }

    @Override
    public List<PersonHistoryVO> collectPersonWellOutAbnormalDetailed(String mineCode, Date wellOutTime) {
        String strWellOutTime = DateUtil.formatDateTime(wellOutTime);
        String sql = "select %s from jxry.dwd_jxry_his_personnelinfo h left join jxry.dwd_jxry_base_personnelinfo b " +
                " on h.staff_code = b.staff_code " +
                " where h.flag_bit = '2' and h.out_time is not null ";

        String columns = "h.staff_code AS staffCode, " +
        " b.staff_name AS staffName, " +
                " b.duty AS workType, " +
                " b.dept_name AS departmentName, " +
                " h.mine_leader AS isLeader, " +
                " h.in_time AS wellTime, " +
                " h.out_time AS wellOutTime ";
        StringBuilder sb = new StringBuilder(sql);
        if (StringUtils.isNotBlank(strWellOutTime)) {
            sb.append(" and h.in_time = to_timestamp('").append(strWellOutTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        if (StringUtils.isNotBlank(mineCode)) {
            sb.append(" and h.mine_code in ('").append(getMineCodes(mineCode)).append("' )");
        }

        sb.append(" order by h.in_time desc ");
        List<PersonHistoryVO> query = testJdbcTemplate.query(String.format(sb.toString(), columns),
                new BeanPropertyRowMapper<>(PersonHistoryVO.class));
        return query;
    }

    @Override
    public List<CollectMineLeaderBlankVO> collectMineLeaderBlankStatistics(String mineCode, String startDate, String endDate) {
        String sql = "SELECT %s " +
                "FROM znjc.dws_jxry_his_noleader_offduty_day " +
                "where 1=1 ";

        String columns = "sum(duration) as maxNoLeaderDuration, mine_code as id, left(cast(begin_time as varchar), 10) as maxNoLeaderDurationDate ";
        StringBuilder sb = new StringBuilder(sql);
        if (StringUtils.isNotBlank(startDate)) {
            sb.append(" and begin_time >= to_timestamp('").append(startDate).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        if (StringUtils.isNotBlank(endDate)) {
            sb.append(" and begin_time < to_timestamp('").append(endDate).append("','yyyy-mm-dd hh24:mi:ss') ");
        }

        if (StringUtils.isNotBlank(mineCode)) {
            sb.append(" and mine_code = ('").append(getMineCodes(mineCode)).append("' )");
        }
        sb.append("group by mine_code, left(cast(begin_time as varchar), 10) ");
        sb.append(" order by mine_code desc ");
        List<CollectMineLeaderBlankVO> query = jdbcTemplate.query(String.format(sb.toString(), columns),
                new BeanPropertyRowMapper<>(CollectMineLeaderBlankVO.class));
        if (query.size() > 0) {
            query.forEach(q -> {
                q.setName(setMineName(q.getId()));
                q.setCountyName(setCompanyName(q.getId()));
            });
        }
        return query;
    }

    @Override
    public List<CollectMineLeaderDetailVO> collectMineLeaderDetail(String mineCode, String startTime, String endTime) {
        String sql = "SELECT %s " +
                "FROM znjc.dws_jxry_his_noleader_offduty_day " +
                "where 1=1 ";

        String columns = "duration as maxNoLeaderDuration, mine_code as id, begin_time as startTime ";
        StringBuilder sb = new StringBuilder(sql);
        if (StringUtils.isNotBlank(startTime)) {
            sb.append(" and begin_time >= to_timestamp('").append(startTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        if (StringUtils.isNotBlank(endTime)) {
            sb.append(" and begin_time < to_timestamp('").append(endTime).append("','yyyy-mm-dd hh24:mi:ss') ");
        }

        if (StringUtils.isNotBlank(mineCode)) {
            sb.append(" and mine_code in ( '").append(getMineCodes(mineCode)).append("' )");
        }
        sb.append(" order by begin_time desc ");
        List<CollectMineLeaderDetailVO> query = jdbcTemplate.query(String.format(sb.toString(), columns),
                new BeanPropertyRowMapper<>(CollectMineLeaderDetailVO.class));
        if (query.size() > 0) {
            // todo 井下最大最小人数不知道

            query.forEach(q -> {
                q.setName(setMineName(mineCode));
                q.setCountyName(setCompanyName(mineCode));
            });
        }
        return query;
    }

    @Override
    public Map<String, Object> chartSumLeaderBlank(String mineCode, String startDate, String endDate) {

        String sql = "select left(cast(begin_time as varchar), 10) as days, count(distinct(mine_code)) cnt " +
                "from znjc.dws_jxry_his_noleader_offduty_day " +
                "where 1=1 ";

        StringBuilder sb = new StringBuilder(sql);
        if (StringUtils.isNotBlank(startDate)) {
            sb.append(" and begin_time >= to_timestamp('").append(startDate).append("','yyyy-mm-dd hh24:mi:ss') ");
        }
        if (StringUtils.isNotBlank(endDate)) {
            sb.append(" and begin_time < to_timestamp('").append(endDate).append("','yyyy-mm-dd hh24:mi:ss') ");
        }

        if (StringUtils.isNotBlank(mineCode)) {
            sb.append(" and mine_code in ( '").append(getMineCodes(mineCode)).append("' )");
        }

        sb.append("group by left(cast(begin_time as varchar), 10) ");
        List<ChartMineLeaderBlankVO> coalmines = jdbcTemplate.query(sb.toString(),
                new BeanPropertyRowMapper<>(ChartMineLeaderBlankVO.class));

        Map<String, List> ret = Maps.newHashMap();

        List<String> dateRange = DateRangeUtil.getDateRange(startDate, endDate, "yyyy-MM-dd");
        Map<String, Integer> dateRowMap = coalmines.stream().collect(
                Collectors.toMap(ChartMineLeaderBlankVO::getDays, ChartMineLeaderBlankVO::getCnt));


        List<Integer> lstCount = Lists.newArrayList();
        for (String date : dateRange) {
            Integer cnt = dateRowMap.getOrDefault(date, 0);
            lstCount.add(cnt);
        }
        ret.put("xName", dateRange);
        ret.put("noLeaderMineCount", lstCount);
        return Dict.create().set("content", ret).set("totalElements", dateRange.size());
    }

    private String getMineCodes(String mineCode) {

        //#华能煤业 18位ID： 91110000592360113C
        //#华亭公司 18位ID：916200002258722645
        //#砚北煤矿    12位ID：620824009734
        //#庆阳公司 18位ID：91621000566400003E
        //#核桃峪煤矿 12位ID：621025009717
        //#北能公司 18位ID： 91150100767874571R
        //#高头窑煤矿 12位ID：150621011038
        //#扎煤公司 18位ID：91150781720169744B
        //#灵露煤矿    12位ID：150781013671

        List<String> lstMineCodes = new ArrayList<>();
        switch(mineCode) {
            case "91110000592360113C":
                lstMineCodes.add("620824009734");
                lstMineCodes.add("621025009717");
                lstMineCodes.add("150621011038");
                lstMineCodes.add("150781013671");
                break;
            case "916200002258722645":
                lstMineCodes.add("620824009734");
                break;
            case "91621000566400003E":
                lstMineCodes.add("621025009717");
                break;
            case "91150100767874571R":
                lstMineCodes.add("150621011038");
                break;
            case "91150781720169744B":
                lstMineCodes.add("150781013671");
                break;
            default:
                lstMineCodes.add(mineCode);
        }

        return StringUtils.join(lstMineCodes,"','");

    }

    private static String setMineName(String mineCode) {
        Map<String, String> MINECODE_NAME_MAP = new HashMap<String, String>() {
            {
                //#华能煤业 18位ID： 91110000592360113C
                //#华亭公司 18位ID：916200002258722645
                //#砚北煤矿    12位ID：620824009734
                //#庆阳公司 18位ID：91621000566400003E
                //#核桃峪煤矿 12位ID：621025009717
                //#北能公司 18位ID： 91150100767874571R
                //#高头窑煤矿 12位ID：150621011038
                //#扎煤公司 18位ID：91150781720169744B
                //#灵露煤矿    12位ID：150781013671
                put("620824009734", "砚北煤矿");
                put("621025009717", "核桃峪煤矿");
                put("150621011038", "高头窑煤矿");
                put("150781013671", "灵露煤矿");
            }
        };
        return MINECODE_NAME_MAP.get(mineCode);
    }
    private static String setCompanyName(String mineCode) {
        Map<String, String> MINECODE_NAME_MAP = new HashMap<String, String>() {
            {
                //#华能煤业 18位ID： 91110000592360113C
                //#华亭公司 18位ID：916200002258722645
                //#砚北煤矿    12位ID：620824009734
                //#庆阳公司 18位ID：91621000566400003E
                //#核桃峪煤矿 12位ID：621025009717
                //#北能公司 18位ID： 91150100767874571R
                //#高头窑煤矿 12位ID：150621011038
                //#扎煤公司 18位ID：91150781720169744B
                //#灵露煤矿    12位ID：150781013671
                put("620824009734", "华亭公司");
                put("621025009717", "庆阳公司");
                put("150621011038", "北能公司");
                put("150781013671", "扎煤公司");
            }
        };
        return MINECODE_NAME_MAP.get(mineCode);
    }

}
